﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using MM.Entities;

namespace MM.Business
{
    public class clsPHIEU_YEU_CAU
    {
        public clsPHIEU_YEU_CAU(string sConn)
        {
            SqlHelper.ConnectionString = sConn;
        }
        #region Private methods
        private PHIEU_YEU_CAU GetDataRow(DataRow row)
        {
            PHIEU_YEU_CAU objPHIEU_YEU_CAU = new PHIEU_YEU_CAU();
            try
            {
                if (row["MaPhieuYeuCau"] != DBNull.Value)
                    objPHIEU_YEU_CAU.MaPhieuYeuCau = row["MaPhieuYeuCau"].ToString();
            }
            catch { }
            try
            {
                if (row["NGAYTAO"] != DBNull.Value)
                    objPHIEU_YEU_CAU.NgayTao = Convert.ToDateTime((row["NGAYTAO"]));
            }
            catch { }
            try
            {
                if (row["NguoiTao"] != DBNull.Value)
                    objPHIEU_YEU_CAU.NguoiTao = (row["NguoiTao"].ToString());
            }
            catch { }
            try
            {
                if (row["MaTau"] != DBNull.Value)
                    objPHIEU_YEU_CAU.MaTau = row["MaTau"].ToString();
            }
            catch { }
            try
            {
                if (row["TinhTrang"] != DBNull.Value)
                    objPHIEU_YEU_CAU.TinhTrang = int.Parse(row["TinhTrang"].ToString());
            }
            catch { }
            try
            {
                if (row["SoLanExport"] != DBNull.Value)
                    objPHIEU_YEU_CAU.SoLanExport = int.Parse(row["SoLanExport"].ToString());
            }
            catch { }
            try
            {
                if (row["NgayExport"] != DBNull.Value)
                    objPHIEU_YEU_CAU.NgayExport = Convert.ToDateTime(row["NgayExport"]);
            }
            catch { }
            try
            {
                if (row["NguoiExport"] != DBNull.Value)
                    objPHIEU_YEU_CAU.NguoiExport = row["NguoiExport"].ToString();
            }
            catch { }
            try
            {
                if (row["DaDuyet"] != DBNull.Value)
                    objPHIEU_YEU_CAU.DaDuyet = int.Parse(row["DaDuyet"].ToString());
            }
            catch { }

            return objPHIEU_YEU_CAU;
        }
        private List<PHIEU_YEU_CAU> GetPHIEU_YEU_CAU(DataTable dt)
        {
            List<PHIEU_YEU_CAU> lst = new List<PHIEU_YEU_CAU>();
            PHIEU_YEU_CAU obj;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj = this.GetDataRow(dt.Rows[i]);
                lst.Add(obj);
            }
            return lst;
        }
        #endregion Private Method

        #region Public Methods
        public DataTable usp_SelectPHIEU_YEU_CAU(string MaPhieuYeuCau)
        {
            string spName = "usp_SelectPHIEU_YEU_CAU";
            DataTable dt = new DataTable();

            try
            {
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaPhieuYeuCau", MaPhieuYeuCau);
               
                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }
               
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return dt;
        }
        public List<PHIEU_YEU_CAU> SelectPHIEU_YEU_CAU(string MaPhieuYeuCau)
        {
            List<PHIEU_YEU_CAU> lst = new List<PHIEU_YEU_CAU>();
            DataTable dt = new DataTable();
            dt = this.usp_SelectPHIEU_YEU_CAU(MaPhieuYeuCau);
            lst = this.GetPHIEU_YEU_CAU(dt);

            return lst;
        }

        public List<PHIEU_YEU_CAU> SelectPHIEU_YEU_CAU_MAX(string matau)
        {
            List<PHIEU_YEU_CAU> lst = new List<PHIEU_YEU_CAU>();
            DataTable dt = new DataTable();

            try
            {
                string spName = "SELECT TOP 1 * FROM PHIEU_YEU_CAU WHERE tinhtrang=0 and maphieuyeucau like '%"+matau+"%' order by maphieuyeucau desc";
                SqlHelper.OpenConnection();



                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, CommandType.Text);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, CommandType.Text);
                }

            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }

            lst = this.GetPHIEU_YEU_CAU(dt);

            return lst;
        }

        public DataTable usp_SelectPHIEU_YEU_CAUsDynamic(string WhereCondition, string OrderByExpression)
        {
            string spName = "usp_SelectPHIEU_YEU_CAUsDynamic";
            DataTable dt = new DataTable();

            try
            {
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@WhereCondition", WhereCondition);
                par[1] = new SqlParameter("@OrderByExpression", OrderByExpression == "" ? null : OrderByExpression);

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }

            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return dt;
        }
        public List<PHIEU_YEU_CAU> SelectPHIEU_YEU_CAUsDynamic(PHIEU_YEU_CAU obj)
        {
            string WhereCondition="";
            string OrderByExpression = "";
            List<PHIEU_YEU_CAU> lst = new List<PHIEU_YEU_CAU>();
            DataTable dt = new DataTable();


            dt = this.usp_SelectPHIEU_YEU_CAUsDynamic(WhereCondition, OrderByExpression);
            lst = this.GetPHIEU_YEU_CAU(dt);

            return lst;
        }

        public List<PHIEU_YEU_CAU> SelectPHIEU_YEU_CAUsSearch(PHIEU_YEU_CAU obj,List<VAT_TU_PHU_TUNG> lstVTPT,int DaDuyet)
        {
            string spName = "usp_SelectPHIEU_YEU_CAUsSearch";
            DataTable dt = new DataTable();
            List<PHIEU_YEU_CAU> lst = new List<PHIEU_YEU_CAU>();
            string temp="";
            try
            {
                for (int i=0;i<lstVTPT.Count;i++)
                {
                    if (i == 0)
                        temp = "(";
                    temp += "'" + lstVTPT[i]._maVTPT + "'";
                    if (i < lstVTPT.Count - 1)
                        temp += ",";
                    else
                        temp += ")";
                }
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[7];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau==""? null:obj.MaPhieuYeuCau);
                if (obj.NgayTao.Year<2012)
                    par[1] = new SqlParameter("@NgayTao", null);
                else
                    par[1] = new SqlParameter("@NgayTao",obj.NgayTao );
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiTao==""?null:obj.NguoiTao);
                if (obj.TinhTrang==-1)
                    par[3] = new SqlParameter("@TinhTrang",null );
                else
                    par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);

                par[4] = new SqlParameter("@DaDuyet", DaDuyet);
                if (obj.NgayTaoTo.Year < 2012)
                    par[5] = new SqlParameter("@NgayTaoTo", null);
                else
                    par[5] = new SqlParameter("@NgayTaoTo", obj.NgayTaoTo);
                par[6] = new SqlParameter("@MaVTPTs",temp==""?null:temp );

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }
                lst = this.GetPHIEU_YEU_CAU(dt);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }           

            return lst;
        }

        public string usp_InsertPHIEU_YEU_CAU(PHIEU_YEU_CAU obj)
        {
            string iResult = "";
            string spName = "usp_InsertPHIEU_YEU_CAU";

            try
            {
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau);
                par[0].Direction = ParameterDirection.Output;
                par[1] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiTao);
                par[3] = new SqlParameter("@MaTau", obj.MaTau);
                par[4] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[5] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[6] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[7] = new SqlParameter("@NguoiExport", obj.NguoiExport);

                if (SqlHelper.IsHaveTran())
                     SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                     SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);
                 iResult = par[0].Value.ToString();
                
            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public string usp_InsertPHIEU_YEU_CAU_CHI_TIET_PYC(PHIEU_YEU_CAU obj,List<CHI_TIET_PYC> lst)
        {
            long iResult = 0;
            string spName = "usp_InsertPHIEU_YEU_CAU";
            string MaPhieuYeuCau = "";
            try
            {
                SqlHelper.BeginTran();
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@MaPhieuYeuCau",SqlDbType.VarChar,10);
                par[0].Direction = ParameterDirection.Output;
                
                par[1] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiTao);
                par[3] = new SqlParameter("@MaTau", obj.MaTau);
                par[4] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[5] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[6] = new SqlParameter("@NgayExport", null);
                par[7] = new SqlParameter("@NguoiExport", obj.NguoiExport);

               
                iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                MaPhieuYeuCau = par[0].Value.ToString();
                if (MaPhieuYeuCau.Length!=10)
                {                    
                    SqlHelper.Tran.Rollback();
                    iResult = -1;
                }
                else
                {

                    //spName = "usp_InsertCHI_TIET_PYC"; chỉnh ngày 29102013
                    CHI_TIET_PYC objCT;
                    for (int i = 0; i < lst.Count; i++)
                    {
                        spName = "usp_InsertCHI_TIET_PYC"; // chỉnh ngày 29102013
                        objCT = new CHI_TIET_PYC();
                        objCT = lst[i];
                        objCT.MaPhieuYeuCau = MaPhieuYeuCau;
                        par = new SqlParameter[10];
                        par[0] = new SqlParameter("@MaChiTiet", objCT.MaChiTiet);
                        par[0].Direction = ParameterDirection.Output;
                        par[1] = new SqlParameter("@MaThietBi", objCT.MaThietBi);
                        par[2] = new SqlParameter("@MaPhieuYeuCau", objCT.MaPhieuYeuCau);
                        par[3] = new SqlParameter("@TinhTrang", objCT.TinhTrang);
                        par[4] = new SqlParameter("@SoLuong", objCT.SoLuong);
                        par[5] = new SqlParameter("@NgayGiaoHang", objCT.NgayGiaoHang);
                        par[6] = new SqlParameter("@NguoiTao", obj.NguoiTao);
                        par[7] = new SqlParameter("@NgayTao", obj.NgayTao);
                        par[8] = new SqlParameter("@MaTrangThai", objCT.oTRANG_THAI_VTPT.MaTrangThai);
                        par[9] = new SqlParameter("@GhiChu", objCT.GhiChu);
                        
                        SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                        
                        iResult = (int)par[0].Value;

                        if (iResult <= 0)
                        {
                            SqlHelper.Tran.Rollback();
                            iResult = -2;
                            MaPhieuYeuCau = "";
                            break;
                        }
                        else // insert Component
                        {
                            if (objCT.MaTrangThai == 1) // gia cong
                            {
                                objCT.MaChiTiet = (int)iResult;
                                for (int c = 0; c < objCT.lstComponent.Count; c++)
                                {
                                    spName = "usp_InsertCHI_TIET_PYC_COMPONENT";
                                    par = new SqlParameter[5];
                                    par[0] = new SqlParameter("@MaChiTietPYC", objCT.MaChiTiet);
                                    par[1] = new SqlParameter("@MaThietBi", objCT.lstComponent[c].MaThietBi);
                                    par[2] = new SqlParameter("@SoLuong", objCT.lstComponent[c].SoLuong);
                                    par[3] = new SqlParameter("@Plant", objCT.lstComponent[c].Plant);
                                    par[4] = new SqlParameter("@MaComponent", SqlDbType.BigInt);
                                    par[4].Direction = ParameterDirection.Output;

                                    SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                                    iResult = (long)par[4].Value;
                                    if (iResult <= 0)
                                    {
                                        SqlHelper.Tran.Rollback();
                                        iResult = -2;
                                        MaPhieuYeuCau = "";
                                        break;
                                    }
                                }
                            }
                        }
                    }
                    if (iResult >= 0)
                        SqlHelper.Tran.Commit();

                }

            }
            catch (Exception ex)
            {
                SqlHelper.Tran.Rollback();
                SqlHelper.CloseConnection();
                MaPhieuYeuCau = "";
            }
            return MaPhieuYeuCau;
        }

        public int usp_UpdatePHIEU_YEU_CAU(PHIEU_YEU_CAU obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_YEU_CAU";

            try
            {
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau);
                par[1] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[2] = new SqlParameter("@NguoiTao", obj.NguoiTao);
                par[3] = new SqlParameter("@MaTau", obj.MaTau);
                par[4] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[5] = new SqlParameter("@SoLanExport", obj.SoLanExport);
                par[6] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[7] = new SqlParameter("@NguoiExport", obj.NguoiExport);

                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch (Exception ex)
            {
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_YEU_CAU_Duyet(PHIEU_YEU_CAU obj)
        {
            int iResult = -1;
            string spName = "usp_UpdatePHIEU_YEU_CAU_Duyet";

            try
            {
                SqlParameter[] par = new SqlParameter[6];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau);
                par[1] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[2] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[3] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[4] = new SqlParameter("@NguoiTao", obj.NguoiTao);
                par[5] = new SqlParameter("@DaDuyet", obj.DaDuyet);
                

                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch
            {
                iResult = -1;
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_YEU_CAU_XuatFile(PHIEU_YEU_CAU obj)
        {
            int iResult = -1;
            string spName = "usp_UpdatePHIEU_YEU_CAU_XUATFILE";

            try
            {
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau);
                par[1] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[2] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[3] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[4] = new SqlParameter("@NguoiTao", obj.NguoiTao);                


                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch
            {
                iResult = -1;
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_YEU_CAU_Xoa(PHIEU_YEU_CAU obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_YEU_CAU_Xoa";

            try
            {
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau);
                par[1] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[2] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[3] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[4] = new SqlParameter("@NguoiTao", obj.NguoiTao);



                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch 
            {
                iResult = -1;
            }
            return iResult;
        }

        public int usp_UpdatePHIEU_YEU_CAU_PhucHoiXoa(PHIEU_YEU_CAU obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_YEU_CAU_PhucHoiXoa";

            try
            {
                SqlParameter[] par = new SqlParameter[5];
                par[0] = new SqlParameter("@MaPhieuYeuCau", obj.MaPhieuYeuCau);
                par[1] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[2] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[3] = new SqlParameter("@NgayTao", obj.NgayTao);
                par[4] = new SqlParameter("@NguoiTao", obj.NguoiTao);



                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch 
            {
                iResult = -1;
            }
            return iResult;
        }

        public int usp_DeletePHIEU_YEU_CAU(string MaPhieuYeuCau)
        {
            int iResult = 0;
            string spName = "usp_DeletePHIEU_YEU_CAU";

            try
            {
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@MaPhieuYeuCau", MaPhieuYeuCau);
               

                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

            }
            catch 
            {
                iResult = -1;
            }
            return iResult;
        }

        public void CsvExportPHIEU_YEU_CAU(PHIEU_YEU_CAU pyc, List<CHI_TIET_PYC> lst,string filepath)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(pyc.MaPhieuYeuCau);
            sb.Append(";");
            sb.Append(pyc.TinhTrang == 0 ? "" : "D");

            if (pyc.TinhTrang == 0)
            {

                for (int i = 0; i < lst.Count; i++)
                {
                    if (lst[i].TinhTrang == 0)
                    {
                        sb.AppendLine();

                        sb.Append(lst[i].MaThietBi);
                        sb.Append(";");
                        sb.Append(lst[i].SoLuong);
                        sb.Append(";");
                        sb.Append("");
                        sb.Append(";");
                        sb.Append(lst[i].oTRANG_THAI_VTPT.ItemCategory);
                        sb.Append(";");

                        string y = lst[i].NgayGiaoHang.Year.ToString();
                        string m = lst[i].NgayGiaoHang.Month.ToString();
                        string d = lst[i].NgayGiaoHang.Day.ToString();
                        m = Convert.ToString("0" + m).Substring(m.Length - 1, 2);
                        d = Convert.ToString("0" + d).Substring(d.Length - 1, 2);

                        sb.Append(d + "." + m + "." + y);
                        sb.Append(";");
                        sb.Append(lst[i].oTRANG_THAI_VTPT.ValuationType.Trim());
                        sb.Append(";");

                        string com = "";
                        for (int j = 0; j < lst[i].lstComponent.Count; j++)
                        {                            
                            com += lst[i].lstComponent[j].MaThietBi;
                            com += "$";
                            com += lst[i].lstComponent[j].SoLuong;
                            com += "$";
                            com += lst[i].lstComponent[j].Plant;

                            if (j < lst[i].lstComponent.Count-1)
                                com += "#";
                        }
                        sb.Append(com);
                        sb.Append(";");
                        sb.Append(lst[i].GhiChu);
                    }
                }
            }

            
            File.WriteAllText(filepath, sb.ToString());
        }

        #endregion Public Methods
    }
}
